Skip to main content

OData

In the world of OData (Open Data Protocol), efficiently querying and interacting with data services is a key capability. This guide will introduce you to the various OData column (property) types and the filter operators that can be applied to them for effective data querying.

OData Column Types

OData services support a range of data types for properties, each designed to accommodate specific kinds of data:

  • Edm.String
    Represents text data. It can contain letters, numbers, and other characters.
  • Edm.Int16, Edm.Int32, Edm.Int64
    Integer data types for representing whole numbers of varying sizes.
  • Edm.Decimal
    Used for decimal numbers, providing a high level of precision.
  • Edm.Double
    Represents floating-point numbers.
  • Edm.Boolean
    Represents boolean values (true or false).
  • Edm.Date
    Used for dates without time information.
  • Edm.TimeOfDay
    Represents time without date information.
  • Edm.DateTimeOffset
    Represents date and time with timezone information.

Filter Operators

OData's powerful query options include a variety of filter operators that can be applied to entity properties in a query. These operators allow for complex filtering based on the property values:

Comparison Operators​

  • eq (equals)
    Checks if the value of a property is equal to a specified value.
  • ne (not equals)
    Checks if the value of a property is not equal to a specified value.
  • gt (greater than), ge (greater than or equal to)
    These operators are used for comparing numerical values, including dates and times.
  • lt (less than), le (less than or equal to)
    Similar to gt and ge, these operators compare numerical values.

Logical Operators​

  • and
    Combines two or more conditions that must all be true for the filter to pass.
  • or
    Combines two or more conditions where at least one must be true for the filter to pass.
  • not
    Negates the condition that follows.

String Functions​

  • startswith, endswith
    Check if a string property starts or ends with a specified substring.
  • contains
    Checks if a string property contains a specified substring.
  • substringof (deprecated in OData version 4.0)
    Similar to contains, checks if a property string contains a specific substring.

Arithmetic Operators​

  • add, sub, mul, div, mod
    Perform basic arithmetic operations on numerical properties.

Date Functions​

  • year, month, day
    Extracts the year, month, or day component from a Date or DateTimeOffset property.

Usage in Queries

This will be mostly useful when using custom queries in javascript within UILogic. Filter operators can be used in the $filter query option to specify conditions that entities must meet to be included in the response. Here's a simple example:

http://example.com/odata/People?$filter=FirstName eq 'John' and Age gt 30

This query retrieves people named "John" who are older than 30.

It's important to note that the availability of certain filter functions and operators may vary depending on the OData service implementation. Always refer to the specific documentation for the OData service you are querying.

By leveraging these column types and filter operators, users can construct powerful queries to efficiently access and manipulate data in an OData service.